package com.eden.service.impl;

import java.awt.Desktop;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;

import com.eden.config.Score;
import com.eden.constant.Constant;
import com.eden.constant.Constant_msg;
import com.eden.context.AppContext;
import com.eden.entity.AnalyzeResult;
import com.eden.service.ExcelService;
import com.eden.ui.Model;
import com.eden.util.TypeConvertUtil;

public class ExcelServiceImpl extends BaseServiceImpl implements ExcelService {

	private final Log log = LogFactory.getLog(ExcelServiceImpl.class) ;
	
	private List<String> baseColumn ;
	
	public File openExcel(String subjectName , String className) {
		Desktop desktop = Desktop.getDesktop() ;
		
		Workbook workbook = new HSSFWorkbook() ;
		Sheet sheet = workbook.createSheet() ;
		Row row = sheet.createRow(0) ;
//		设置表头
		int columnIndex = 0 ;
//		设置基本的列表题
		for(String columnName : baseColumn){
			row.createCell(columnIndex++).setCellValue(columnName) ;
		}
		
		List<Score> scoreList = AppContext.getConfiguration().getScoreList() ;
		for(Score score : scoreList) {
			if(score.isShow()){
				row.createCell(columnIndex++).setCellValue(score.getName() + "(" + score.getPercent() * 100 + "%)") ;
			}
		}
		
		String fileName = subjectName + "-" + className  + DateFormatUtils.format(System.currentTimeMillis(), "yyMMddHHmms") 
				+ ".xls";
		String path = Constant.DATA_PATH + fileName ;
		File file = new File(path) ;
		try {
			FileOutputStream fout = new FileOutputStream(file) ;
			workbook.write(fout ) ;
			fout.close() ;
			log.info("open the data file ") ;
			desktop.edit(file) ;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
		}
		
		return file;
	}
	
	public void handle(File file){
		
	}
	
	public Model readExcel(File file , List<Score> scoreList)  {
		Model model = new Model() ;
		try {
			Workbook book = new HSSFWorkbook(new FileInputStream(file)) ;
			Sheet sheet = book.getSheetAt(0) ;
			log.debug("first row number : " + sheet.getFirstRowNum() ) ;
			log.debug("top row number : " + sheet.getTopRow()  ) ;
			
//			列名称
			List<String> columnNames = new ArrayList<String>() ;
			columnNames.addAll(baseColumn) ;
			for(Score score : scoreList) {
				if(score.isShow()) {
					columnNames.add(score.getName()) ;
				}
			}
			
//			列索引与分数分布名称对应值
			Map<Integer , String> columnIndexMap = new HashMap<Integer, String>() ;
			Row firstRow = sheet.getRow(0) ;
			int cellIndex = 0 ;
			for(Iterator<Cell> iterator = firstRow.cellIterator();iterator.hasNext() ;){
				String cellValue = iterator.next().getStringCellValue() ;
				for(String columnName : columnNames) {
					if (StringUtils.contains(cellValue , columnName)) {
						columnIndexMap.put(cellIndex++, columnName) ;
					}
				}
			}
			log.info("column name and index relationship :" + columnIndexMap) ;
			
			
			if(columnIndexMap.size() == 0) {
//				数据列错误
				model.addErr(Constant_msg.INPUT_DATA_COLUMN_ERR) ;
				return model ;
			}
			
			log.info("read data in excel") ;
			List<Map<String , Object>> resultList = new ArrayList<Map<String , Object>>() ;
			Map<String , Object> item = null ;
			Row currRow = null ;
			Cell currCell = null ;
			for(Iterator<Row> iterator = sheet.rowIterator() ; iterator.hasNext();){
				currRow = iterator.next() ;
				item = new HashMap<String, Object>() ;
				for(Iterator<Cell> cellIterator = currRow.cellIterator() ; cellIterator.hasNext();){
					currCell = cellIterator.next() ;
					item.put(columnIndexMap.get(currCell.getColumnIndex()), currCell) ;
				}
				log.info("read score excel row: " + currRow.getRowNum() + item) ;
				resultList.add(item) ;
			}
//			return resultList ;
			//resultList 第一列为标题
			if(resultList.size() < 2) {
//				没有读取到成绩
				model.addErr(Constant.INPUT_SCORE_DATA) ;
			}
			model.setAttribute(Constant.READ_EXCEL_RESULT_LIST_KEY, resultList) ;
			return model ;
		} catch(IOException e){
			log.error("read score excel error " , e) ;
		} catch (Exception e) {
			log.error("read score excel error " , e) ;
		}
		return model ;
	}
	
	public List<Map<String, Object>> readExcel(String fileName) {
		return null ;
	}
	
	@SuppressWarnings("unchecked")
	public Model createExcel(Model model, File chartFile , String className , String subjectName ) {
		try{
//			String className = TypeConvertUtil.toStr(AppContext.getAttribute(Constant.CURR_CLASS_NAME_KEY)) ;
//			String subjectName = TypeConvertUtil.toStr(AppContext.getAttribute(Constant.CURR_SUBJECT_NAME_KEY)) ;
			
			Workbook book = new HSSFWorkbook(new FileInputStream(Constant.TEMPLATE_FILE)) ;
			Sheet analyzeSheet = book.getSheetAt(0) ;
//			设置班级,课程名称
			analyzeSheet.getRow(2).getCell(1).setCellValue(className) ;
			analyzeSheet.getRow(2).getCell(4).setCellValue(subjectName) ;
			
			AnalyzeResult ar = (AnalyzeResult)model.getAttrbute(Constant.ANALYZE_RESULT_KEY) ;
			analyzeSheet.getRow(7).getCell(1).setCellValue((ar).getMaxScore()) ;
			analyzeSheet.getRow(7).getCell(3).setCellValue((ar).getMinScore()) ;
			analyzeSheet.getRow(7).getCell(5).setCellValue((ar).getAverage()) ;
			analyzeSheet.getRow(7).getCell(7).setCellValue((ar).getStandardDeviation()) ;
			analyzeSheet.getRow(8).getCell(1).setCellValue((ar).getPassRate()) ;
			analyzeSheet.getRow(8).getCell(3).setCellValue((ar).getGoodRate()) ;
			analyzeSheet.getRow(8).getCell(5).setCellValue((ar).getDifferentiation()) ;
			analyzeSheet.getRow(8).getCell(7).setCellValue((ar).getDifficulty()) ;
			
			List<Map<String, Double>> scoreList = (List<Map<String, Double>>) model.getAttrbute(Constant.SCORE_LIST_KEY) ;
			//第一行为表头(分数分布)
			int num = scoreList.size()- 1 ;
			Map<String, Double> totalScoreDistMap = ((Map<String, Map<String , Double>>)model.getAttrbute(Constant.SCORE_DISTRIBUTE_KEY)).get(Constant.TOTAL_SCORE) ;			
			
			double num60 = TypeConvertUtil.toDouble(totalScoreDistMap.get("30以下")) + TypeConvertUtil.toDouble(totalScoreDistMap.get("30-40")) 
					+ TypeConvertUtil.toDouble(totalScoreDistMap.get("40-50") )  + TypeConvertUtil.toDouble(totalScoreDistMap.get("50-60") );
			double num70 = TypeConvertUtil.toDouble(totalScoreDistMap.get("60-70") );
			double num80 = TypeConvertUtil.toDouble(totalScoreDistMap.get("70-80") );
			double num90 = TypeConvertUtil.toDouble(totalScoreDistMap.get("80-90") );
			double num100 = TypeConvertUtil.toDouble(totalScoreDistMap.get("90以上") );
			analyzeSheet.getRow(11).getCell(3).setCellValue(num60) ;
			analyzeSheet.getRow(11).getCell(4).setCellValue(num70) ;
			analyzeSheet.getRow(11).getCell(5).setCellValue(num80) ;
			analyzeSheet.getRow(11).getCell(6).setCellValue(num90) ;
			analyzeSheet.getRow(11).getCell(7).setCellValue(num100) ;
			
			analyzeSheet.getRow(12).getCell(3).setCellValue(num60/num * 100) ;
			analyzeSheet.getRow(12).getCell(4).setCellValue(num70/num * 100) ;
			analyzeSheet.getRow(12).getCell(5).setCellValue(num80/num * 100) ;
			analyzeSheet.getRow(12).getCell(6).setCellValue(num90/num * 100) ;
			analyzeSheet.getRow(12).getCell(7).setCellValue(num100/num * 100) ;
			
//			图片
			// analyzeSheet.getRow(13).getCell(1). ;
			if (chartFile != null) {
				int chartFileIndex = book.addPicture(
						IOUtils.toByteArray(new FileInputStream(chartFile)),
						Workbook.PICTURE_TYPE_PNG);
				CreationHelper helper = book.getCreationHelper();
				Drawing drawing = analyzeSheet.createDrawingPatriarch();
				// add a picture shape
				ClientAnchor anchor = helper.createClientAnchor();
				anchor.setCol1(1);
			    anchor.setRow1(13);anchor.setDx1(10);
				Picture pict = drawing.createPicture(anchor, chartFileIndex);
				// auto-size picture relative to its top-left corner
				pict.resize();
			}
			
//			计算机测评结果
			analyzeSheet.getRow(28).getCell(1).setCellValue(ar.getEvaluation()) ;
			
			analyzeSheet.getRow(30).getCell(6).setCellValue(DateFormatUtils.format(System.currentTimeMillis(), "yyyy-MM-dd")) ;
			
			log.info(analyzeSheet.getRow(0).getCell(0).getStringCellValue()) ;
			
			String resultFileName = Constant.RESULT_PATH + className + subjectName 
					+ DateFormatUtils.format(System.currentTimeMillis(), "yyMMddHHmms") + ".xls";
			File resultFile = new File(resultFileName) ;
			FileOutputStream fout = new FileOutputStream(resultFile) ;
			book.write(fout) ;
			fout.flush() ;
			fout.close() ;
			
			model.setAttribute(Constant.RESULT_FILE_KEY, resultFile) ; 
			
			return model ;
		} catch(FileNotFoundException e){
			log.error("" , e) ;
		} catch(Exception e) {
			log.error("" , e) ;
		}
		return null;
	}
	
//------------------------------------------------------------------------
	public List<String> getBaseColumn() {
		return baseColumn;
	}

	public void setBaseColumn(List<String> baseColumn) {
		this.baseColumn = baseColumn;
	}

}
